package dao.impl;

import bean.Grade;
import bean.Student;
import bean.StudentStateEnum;
import dao.StudentDao;
import utils.DruidUtils;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class StudentDaoimpl extends DruidUtils implements StudentDao {


    @Override
    public List<Student> getstulist(String stuname,String stuno,int sex,int index,int lines) {

            String sql = "select * from student s,grade g where g.gradeid = s.gid and state!=4" ;
            StringBuffer sb = new StringBuffer(sql);
            ArrayList list = new ArrayList();
            if (stuname!=null&&stuname.length()>0){
                sb.append(" and stuname like ?");
                list.add("%"+stuname+"%");
            }
            if (stuno!=null&&stuno.length()>0){
            sb.append(" and stuno = ?");
            list.add(stuno);
            }
            if (sex!=-1){
                sb.append(" and s.sex=?");
                list.add(sex);
            }
            sb.append(" limit ?,?");
            list.add((index-1)*lines);
        list.add(lines);
            rs = query(sb.toString(), list);
            List<Student> students = new ArrayList<>();
        System.out.println(sb);
            try {
                while (rs.next()){
                    Student student = new Student() ;
                    student.setStuid(rs.getInt("stuid"));
                    student.setStuname(rs.getString("stuname"));
                    student.setStuno(rs.getString("stuno"));
                    student.setSex(rs.getInt("sex"));
                    student.setPhone(rs.getString("phone"));
                    student.setEmail(rs.getString("email"));
                    student.setRegistered(rs.getString("registered"));
                    student.setAddress(rs.getString("address"));
                    student.setProfession(rs.getString("profession"));
                    student.setIdnumber(rs.getString("idnumber"));
                    student.setPolitics(rs.getString("politics"));
                    student.setRegdate(rs.getDate("regdate"));
                    student.setState(rs.getInt("state"));
                    student.setIntroduction(rs.getString("introduction"));
                    student.setGid(rs.getInt("gid"));
                    Grade grade = new Grade();
                    grade.setGradeid(rs.getInt("gradeid"));
                    grade.setGradeName(rs.getString("gradeName"));
                    student.setGrade(grade);
                    students.add(student);
                }

            } catch (SQLException throwables) {
                throwables.printStackTrace();
            } finally {
                closeall();
            }
            return students;
    }

    @Override
    public int gettotal(String stuname, String stuno, int i) {
        String sql = "select count(*) from student s,grade g where g.gradeid = s.gid and state!=4" ;
        StringBuffer sb = new StringBuffer(sql);
        ArrayList list = new ArrayList();
        if (stuname!=null&&stuname.length()>0){
            sb.append(" and stuname like ?");
            list.add("%"+stuname+"%");
        }
        if (stuno!=null&&stuno.length()>0){
            sb.append(" and stuno = ?");
            list.add(stuno);
        }
        if (i!=-1){
            sb.append(" and s.sex=?");
            list.add(i);
        }

        rs = query(sb.toString(), list);
        int total =0;
        try {
            while (rs.next()){
                total = rs.getInt(1);
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            closeall();
        }
        return total;

    }

    @Override
    public int addstu(String stuname,String stuno,String address,String email,String phone,String idnumber,
                       String politics,String registered,String profession,String introduction,int gid,int sex) {
        String sql = "insert into student(stuname,stuno,sex,phone,email,registered,address,profession,idnumber,politics,introduction,gid) values(?,?,?,?,?,?,?,?,?,?,?,?)";
        ArrayList list = new ArrayList();

        list.add(stuname);
        list.add(stuno);
        list.add(sex);
        list.add(phone);
        list.add(email);
        list.add(registered);
        list.add(address);
        list.add(profession);
        list.add(idnumber);
        list.add(politics);

        list.add(introduction);
        list.add(gid);
        k = update(sql, list);
        closeall();
        if (k>0){
            return k;
        }else{
            return 0;
        }


    }

    @Override
    public Student getStudentById(int stuid) {
        Student student = null;
        try {
            String sql = "select * from student where stuid = ?";
            ArrayList list = new ArrayList();
            list.add(stuid);
            rs = query(sql, list);
            student = new Student();
            while(rs.next()){
                student.setStuid(rs.getInt("stuid"));
                student.setStuname(rs.getString("stuname"));
                student.setStuno(rs.getString("stuno"));
                student.setSex(rs.getInt("sex"));
                student.setPhone(rs.getString("phone"));
                student.setEmail(rs.getString("email"));
                student.setRegistered(rs.getString("registered"));
                student.setAddress(rs.getString("address"));
                student.setProfession(rs.getString("profession"));
                student.setIdnumber(rs.getString("idnumber"));
                student.setPolitics(rs.getString("politics"));
                student.setRegdate(rs.getDate("regdate"));
                student.setState(rs.getInt("state"));
                student.setIntroduction(rs.getString("introduction"));
                student.setGid(rs.getInt("gid"));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            closeall();
        }

        return student;
    }

    @Override
    public int updateStudent(int stuid, String stuname, String stuno, String address, String email, String phone, String idnumber,
                             String politics, String registered, String profession, String introduction, int gid, int sex) {
        String sql = "update student set stuname=?,stuno=? ,address=?,email=?,phone=?,idnumber=?,politics=?,registered=?,profession=?,introduction=?,gid=?,sex=? where stuid = ?";
        ArrayList list = null;
        try {
            list = new ArrayList();
            list.add(stuname);
            list.add(stuno);
            list.add(address);
            list.add(email);
            list.add(phone);
            list.add(idnumber);
            list.add(politics);
            list.add(registered);
            list.add(profession);
            list.add(introduction);
            list.add(gid);
            list.add(sex);
            list.add(stuid);
            k = update(sql, list);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            closeall();
        }
        return k;
    }

    @Override
    public int deletestudent(int stuid) {
        String sql = "update student set state =? where stuid = ?";
       ArrayList list = new ArrayList();
       list.add(StudentStateEnum.DELETE.type);
       list.add(stuid);
        k = update(sql, list);
        closeall();
        return k;
    }


}
